package sy.service.impl;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import sy.pageModel.BaseEntity;
import sy.pageModel.DataGrid;
import sy.pageModel.DeptPic;
import sy.service.PicServiceI;
import sy.util.StringUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created by Administrator on 2016-06-29.
 */
@Service
public class PicServiceImpl implements PicServiceI {
    @Autowired
    private JdbcTemplate template;

    String date=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
    @Override
    public DataGrid findPicDataGird(final DeptPic pic) {
        DataGrid dg = new DataGrid();
        String sql = "select count(*) from p_dept_pic pd,p_info pi,s_code s where pd.deptid=pi.id and pd.typeid=s.code and s.types='第六边界'  ";
        sql = addWhere(pic, sql);//模糊查询
        long total = template.queryForLong(sql);
        dg.setTotal(total);//获取总数
        sql = "SELECT *from p_dept_pic pd,p_info pi,s_code s where pd.deptid=pi.id and pd.typeid=s.code and s.types='第六边界'   ";
        sql = addWhere(pic, sql);
        sql = addOrderBy(pic, sql);
        sql = addLimit(pic, sql);
        final List<DeptPic> picList = new ArrayList<DeptPic>();
        template.query(sql, new RowCallbackHandler(){
            public void processRow(ResultSet rs) throws SQLException {
                 DeptPic pic1=new DeptPic();
                pic1.setId(rs.getString("pd.id"));
                pic1.setDeptId(rs.getString("pi.id"));
                pic1.setDeptName(rs.getString("pi.name"));
                pic1.setPic(rs.getString("pd.pic"));
                pic1.setOrder(rs.getString("pd.orderid"));
                pic1.setCreateTimeStart(rs.getString("pd.dtime"));
                pic1.setTypeId(rs.getString("s.code"));
                pic1.setTypeName(rs.getString("s.descs"));
                picList.add(pic1);
            }});
        dg.setRows(picList);
        return dg;
    }

    @Override
    public List<BaseEntity> findCodeList(String types) {
        String sql="select *from s_code where types=?";
        final List<BaseEntity> list=new ArrayList<>();
        template.query(sql, new Object[]{types}, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                BaseEntity entity=new BaseEntity();
                entity.setId(rs.getString("code"));
                entity.setName(rs.getString("descs"));
                list.add(entity);
            }
        });
        return list;
    }

    @Override
    @Transactional
    public int addPicService(DeptPic pic) throws Exception {
        String sql="INSERT INTO p_dept_pic(id, deptid, pic, orderid, dtime, typeid) VALUES (?,?,?,?,?,?)";
        int rows=template.update(sql,new Object[]{pic.getId(),pic.getDeptId(),pic.getPic(),pic.getOrder(),date,pic.getTypeId()});
        return rows;
    }

    @Override
    @Transactional
    public int editPicService(DeptPic pic)  throws Exception{
        String sql="UPDATE p_dept_pic set deptid=?,pic=?,orderid=?,dtime=?,typeid=? where id=?";
        int rows=template.update(sql,new Object[]{pic.getDeptId(),pic.getPic(),pic.getOrder(),date,pic.getTypeId(),pic.getId()});
        return rows;
    }

    @Override
    @Transactional
    public int deletePicService(String id) throws Exception {
        String sql="DELETE FROM p_dept_pic where id=?";
        int rows=0;
        String ids[]=id.split(",");
        for (int i = 0; i <ids.length ; i++) {
            rows=template.update(sql,new Object[]{ids[i]});
        }
        return rows;
    }


    private String addLimit(DeptPic pic, String sql) {
        int fromRow = (pic.getPage() - 1) * pic.getRows();
        sql += " limit " + fromRow + "," + pic.getRows();
        return sql;
    }

    private String addWhere(DeptPic pic, String sql) {
        if(!StringUtil.isNullOrEmpty(pic.getDeptId())) {
            sql += " and pd.deptid like '%" + pic.getDeptId() + "%'";
        }
        return sql;
    }


    private String addOrderBy(DeptPic pic, String sql) {
        if (pic.getSort() != null) {
            if("order".equals(pic.getSort())) pic.setSort("pd.orderid");
            if("id".equals(pic.getSort()))  pic.setSort("pd.id");
            if("deptName".equals(pic.getSort())) pic.setSort("pd.deptid");
            if("typeName".equals(pic.getSort())) pic.setSort("pd.typeid");
            if("createTimeStart".equals(pic.getSort()))  pic.setSort("pd.dtime");
            sql += " order by " + pic.getSort() + " " + pic.getOrder();
        }
        return sql;
    }
}
